package com.groot.medical.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.groot.common.util.MedicalDBUtil;
import com.groot.medical.entity.Menu;
import com.groot.medical.entity.SysUser;

public class MenuDaoImpl implements MenuDao {
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;

	@Override
	public int addMenu(Menu menu) {
		int i =0;
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			String sql="insert into t_menu(menu_id,menu_name,menu_pid,menu_url)values(?,?,?,?)";
			ps=conn.prepareStatement(sql);
			ps.setString(1,menu.getMenuId());
			ps.setString(2,menu.getMenuName());
			ps.setString(3, menu.getMenuPid());
			ps.setString(4, menu.getMenuUrl());
			i= ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
			return i;
			
	}

	@Override
	public List<Menu> findAllMenuList() {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		List<Menu> menuList=new ArrayList<Menu>();//创建list集合，用于保持User对象
		try {
		/*conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
*/		String sql="select * from t_menu";
		ps=conn.prepareStatement(sql);
		rs=ps.executeQuery();
		while(rs.next()){
		Menu menu=new Menu();//创建User对象用于保持从数据看查出来的数据
		menu.setMenuId(rs.getString(1));
		menu.setMenuName(rs.getString(2));
		menu.setMenuPid(rs.getString(3));
		menu.setMenuUrl(rs.getString(4));
		menuList.add(menu);
		}
		} catch (SQLException e) {

		e.printStackTrace();
		}
		finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return menuList;
	}

	@Override
	public List<Menu> findMenuListById(String userId) {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		List<Menu> menuList=new ArrayList<Menu>();//创建list集合，用于保持User对象
		try {
		//记得在每一个append前空一格
		StringBuffer sql = new StringBuffer();
		 sql.append(" SELECT ")
			.append(" distinct m.menu_id,m.menu_name,m.menu_pid,m.menu_url")
			.append(" FROM")
			.append(" t_user u,t_user_role ur,t_role r,t_role_menu rm,t_menu m")
			.append(" WHERE")
			.append(" u.user_id=ur.user_id")
			.append(" AND")
			.append(" ur.role_id=r.role_id")
			.append(" AND")
			.append(" r.role_id=rm.role_id")
			.append(" AND")
			.append(" rm.menu_id=m.menu_id")
			.append(" AND")
			.append(" u.user_id='"+userId+"'");
		/*="select distinct m.menu_id,m.menu_pid,m.menu_name FROM t_user u,t_user_role ur,t_role r,t_role_menu rm,t_menu m WHERE u.user_id=ur.user_id AND ur.role_id=r.role_id AND r.role_id=rm.role_id AND rm.menu_id=m.menu_id AND u.user_id='2017';";*/
		ps=conn.prepareStatement(sql.toString());
		rs=ps.executeQuery();
		while(rs.next()){
		Menu menu=new Menu();//创建User对象用于保持从数据看查出来的数据
		menu.setMenuId(rs.getString(1));
		menu.setMenuName(rs.getString(2));
		menu.setMenuPid(rs.getString(3));
		menu.setMenuUrl(rs.getString(4));
		menuList.add(menu);
		}
		} catch (SQLException e) {

		e.printStackTrace();
		}
		finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return menuList;
	}

	@Override
	public List<Menu> findMenuByRoleId(String roleid) {
		// select distinct m.* FROM t_role_menu rm,t_menu m WHERE rm.menu_id=m.menu_id AND rm.role_id='superadmin';
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		List<Menu> menuList=new ArrayList<Menu>();//创建list集合，用于保持User对象
		try {
		//记得在每一个append前空一格
		StringBuffer sql = new StringBuffer();
		 sql.append(" SELECT ")
			.append(" distinct m.*")
			.append(" FROM")
			.append(" t_role_menu rm,t_menu m")
			.append(" WHERE")
			.append(" rm.menu_id=m.menu_id AND rm.role_id='"+roleid+"'");
			
		/*="select distinct m.menu_id,m.menu_pid,m.menu_name FROM t_user u,t_user_role ur,t_role r,t_role_menu rm,t_menu m WHERE u.user_id=ur.user_id AND ur.role_id=r.role_id AND r.role_id=rm.role_id AND rm.menu_id=m.menu_id AND u.user_id='2017';";*/
		ps=conn.prepareStatement(sql.toString());
		rs=ps.executeQuery();
		while(rs.next()){
		Menu menu=new Menu();//创建User对象用于保持从数据看查出来的数据
		menu.setMenuId(rs.getString(1));
		menu.setMenuName(rs.getString(2));
		menu.setMenuPid(rs.getString(3));
		menu.setMenuUrl(rs.getString(4));
		menuList.add(menu);
		}
		} catch (SQLException e) {

		e.printStackTrace();
		}
		finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return menuList;
	}

	@Override
	public int delMenuByRoleId(String roleid) {
		int i =0;
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			String sql="delete from t_role_menu where role_id=?";
			ps=conn.prepareStatement(sql);
			ps.setString(1, roleid);
			i= ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
			return i;
	}

	@Override
	public int getAllMenuCount() {
		int i =0;
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
		conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
		String sql="SELECT count(1) AS id FROM t_menu";
		ps=conn.prepareStatement(sql);
		rs=ps.executeQuery();
		if(rs.next()){
			i=rs.getInt("id");
		}
		} catch (SQLException e) {

		e.printStackTrace();
		}
		finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return i;
		
		
		
	}

	@Override
	public List<Menu> findMenuByPage(int startIndex, int pageSize) {
	
		
		String sql = "SELECT * FROM t_menu ORDER BY CAST(menu_id as SIGNED INTEGER) limit ?,?";
		
		try {
			conn = MedicalDBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, startIndex);
			ps.setInt(2, pageSize);
			rs = ps.executeQuery();
			List<Menu> menuList=new ArrayList<Menu>();
			while(rs.next()){
				/*sysUser = new SysUser(rs.getString(2), rs.getString(3), rs.getString(4));*/
				Menu menu = new Menu();
				menu.setMenuId(rs.getString(1));
				menu.setMenuName(rs.getString(2));
				menu.setMenuPid(rs.getString(3));
				menu.setMenuUrl(rs.getString(4));
				
				menuList.add(menu);
				
			}
			
			return menuList;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return null;
	}

	@Override
	public int findMaxMenuId() {
				int menuId = 0;
			
				try {
					conn = MedicalDBUtil.getConnection();
				conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
				String sql="select MAX(CAST(menu_id as SIGNED INTEGER)) from t_menu";
				ps=conn.prepareStatement(sql);
				rs=ps.executeQuery();
				if(rs.next()){
					menuId=rs.getInt(1);
				}
				} catch (SQLException e) {

				e.printStackTrace();
				}
				finally{
					MedicalDBUtil.closeAll(conn, ps, rs);
				}
				return menuId;
	}

	@Override
	public Menu findMenuByMenuId(String menuid) {
		Menu menu=new Menu();
		//创建list集合，用于保持User对象
		try {
			conn = MedicalDBUtil.getConnection();
		/*conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
*/		String sql="select * from t_menu where menu_id=?";
		ps=conn.prepareStatement(sql);
		ps.setString(1, menuid);
		rs=ps.executeQuery();
		
		while(rs.next()){
		//创建User对象用于保持从数据看查出来的数据
		menu.setMenuId(rs.getString(1));
		menu.setMenuName(rs.getString(2));
		menu.setMenuPid(rs.getString(3));
		menu.setMenuUrl(rs.getString(4));
		
		}
		} catch (SQLException e) {

		e.printStackTrace();
		}
		finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return menu;
	
	}

	@Override
	public void editMenu(Menu menu) {
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			//UPDATE student SET name='"+name+"', age=" + age+" where name='大红'";
			String sql="update t_menu set menu_name=?,menu_url=? where menu_id=?";
			ps=conn.prepareStatement(sql);
			
			ps.setString(1, menu.getMenuName());
			ps.setString(2, menu.getMenuUrl());
			ps.setString(3,menu.getMenuId());
			ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
		
	}

}
